# -*- coding: utf-8 -*-
import sqlite3
yms='2011-01'
yme='2011-02'
yearmonth=''.join(yms.split('-'))
conn=sqlite3.connect(r'cd.db3')
conn.text_factory = str
try:
	conn.execute('''CREATE TABLE ym(
		id INTEGER PRIMARY KEY
	)''')
	conn.execute('''CREATE TABLE rpt_cc(
		countryid INTEGER REFERENCES [country]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
		companyid INTEGER REFERENCES [company]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
		ymid INTEGER REFERENCES [ym]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
		boxs INTEGER,
		cds INTEGER,
		PRIMARY KEY ([countryid],[companyid], [ymid])
	)''')
	conn.commit()
except:
	pass
conn.execute('insert or ignore into ym values(?)',(yearmonth,))

conn.execute('replace into rpt_cc select cd_countryid,cd_companyid,'+yearmonth+',sum(cd_box),count(cd_companyid) from cd where cd_date>=? and cd_date<? group by cd_countryid,cd_companyid order by sum(cd_box) desc',(yms,yme))
conn.commit()
conn.close()
